package database

import (
	"database/sql"
	"encoding/json"
	"fmt"

	"workflow-gui/internal/workflow"
)

// IterationDAO handles requirement iteration-related database operations
type IterationDAO struct {
	db *DB
}

// NewIterationDAO creates a new IterationDAO
func NewIterationDAO(db *DB) *IterationDAO {
	return &IterationDAO{db: db}
}

// CreateIteration records a new iteration for a project
func (dao *IterationDAO) CreateIteration(iter *workflow.RequirementIteration) error {
	adjustmentsJSON, err := json.Marshal(iter.AIAdjustments)
	if err != nil {
		return fmt.Errorf("failed to marshal adjustments: %w", err)
	}

	result, err := dao.db.Exec(`
		INSERT INTO requirement_iterations (project_id, stage, iteration_number, user_feedback, ai_adjustments_json, status)
		VALUES (?, ?, ?, ?, ?, ?)`,
		iter.ProjectID, iter.Stage, iter.IterationNumber, iter.UserFeedback, adjustmentsJSON, iter.Status,
	)
	if err != nil {
		return fmt.Errorf("failed to insert iteration: %w", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return fmt.Errorf("failed to get iteration ID: %w", err)
	}

	iter.ID = int(id)
	return nil
}

// GetIterationsByProject retrieves all iterations for a project, optionally filtered by stage
func (dao *IterationDAO) GetIterationsByProject(projectID int, stage string) ([]workflow.RequirementIteration, error) {
	var query string
	var rows *sql.Rows
	var err error

	if stage != "" {
		query = `SELECT id, project_id, stage, iteration_number, user_feedback, ai_adjustments_json, created_at, status
		         FROM requirement_iterations WHERE project_id = ? AND stage = ? ORDER BY created_at DESC`
		rows, err = dao.db.Query(query, projectID, stage)
	} else {
		query = `SELECT id, project_id, stage, iteration_number, user_feedback, ai_adjustments_json, created_at, status
		         FROM requirement_iterations WHERE project_id = ? ORDER BY created_at DESC`
		rows, err = dao.db.Query(query, projectID)
	}

	if err != nil {
		return nil, fmt.Errorf("failed to query iterations: %w", err)
	}
	defer rows.Close()

	var iterations []workflow.RequirementIteration
	for rows.Next() {
		var iter workflow.RequirementIteration
		var adjustmentsJSON string

		err := rows.Scan(
			&iter.ID, &iter.ProjectID, &iter.Stage, &iter.IterationNumber,
			&iter.UserFeedback, &adjustmentsJSON, &iter.CreatedAt, &iter.Status,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan iteration: %w", err)
		}

		// Parse JSON adjustments
		if err := json.Unmarshal([]byte(adjustmentsJSON), &iter.AIAdjustments); err != nil {
			return nil, fmt.Errorf("failed to parse adjustments JSON: %w", err)
		}

		iterations = append(iterations, iter)
	}

	return iterations, rows.Err()
}

// UpdateIterationStatus updates the status of an iteration (pending → applied/rejected)
func (dao *IterationDAO) UpdateIterationStatus(iterationID int, status string) error {
	_, err := dao.db.Exec(`
		UPDATE requirement_iterations SET status = ? WHERE id = ?`,
		status, iterationID,
	)
	if err != nil {
		return fmt.Errorf("failed to update iteration status: %w", err)
	}

	return nil
}

// GetLatestIterationNumber gets the latest iteration number for a project and stage
func (dao *IterationDAO) GetLatestIterationNumber(projectID int, stage string) (int, error) {
	var iterationNumber sql.NullInt64
	query := `SELECT MAX(iteration_number) FROM requirement_iterations WHERE project_id = ? AND stage = ?`

	err := dao.db.QueryRow(query, projectID, stage).Scan(&iterationNumber)
	if err != nil && err != sql.ErrNoRows {
		return 0, fmt.Errorf("failed to get latest iteration number: %w", err)
	}

	if iterationNumber.Valid {
		return int(iterationNumber.Int64), nil
	}

	return 0, nil // No iterations yet
}
